package com.bbms.dao.impl;

import java.util.HashMap;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.bbms.dao.BorrowRecordDao;
import com.bbms.entity.BorrowRecord;
import com.bbms.entity.Page;
import com.bbms.util.HibernateUtil;
import com.bbms.util.StringUtil;

@Repository("BorrowRecordDaoImpl")
public class BorrowRecordDaoImpl implements BorrowRecordDao{

	@Override
	public void save(BorrowRecord t) {
	}

	@Override
	public void update(BorrowRecord t) {
	}

	/**
	 * 归还图书操作
	 */
	@Override
	public void delete(BorrowRecord t) {
		
		StringBuffer sql=new StringBuffer();
		
		sql.append(  "UPDATE bbms_borrow_record record "
					+"SET record.book_id="+ t.getBookId() +", record.user_reader_id="+ t.getReaderId() +","
					+"record.user_manager_id="+ t.getManagerId() + ",record.borrow_date=record.borrow_date, "
					+"record.restore_date=NOW() , "
					+"record.renew_borrow_num=record.renew_borrow_num , record.`status`= -1  "
					+"WHERE record.id="+ t.getId() +" ");
			
		HibernateUtil.executeSqlUpdate(sql.toString());
		
	}

	@Override
	public Page getPage(int currentPage, int pageSize) {
		return null;
	}

	@Override
	public List<?> query() {
		return null;
	}

	
	/*
	 * 获取分页数据（含查询）
	 * @see com.bbms.dao.BorrowRecordDao#getPage(int, int, java.lang.String, java.lang.String, java.lang.String, java.lang.Integer, java.lang.Integer, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public Page getPage(int currentPage, int pageSize, String s_book, String s_reader, String s_manager,
			Integer s_renewBorrowNum, Integer s_status, String borrowStartTime, String borrowEndTime,
			String restoreStartTime, String restoreEndTime) {
		
		StringBuffer sql=new StringBuffer();
		StringBuffer count=new StringBuffer();
		sql.append(  "SELECT R.id AS id, R.book_id AS bookId, Book.`name` AS bookName, "
					+"R.user_reader_id AS readerId, Reader.`name` AS readerName, "
					+"R.user_manager_id AS managerId, Manager.`name` AS managerName, "
					+"R.borrow_date AS borrowDate, R.restore_date as restoreDate, " 
					+"R.renew_borrow_num AS renewBorrowNum, R.`status` AS `status`, "
					+"CASE WHEN R.renew_borrow_num >=Reader.renewMaxNum THEN -1 ELSE 0 END renewBorrow "
					+"FROM bbms_borrow_record R " 
					+"LEFT JOIN bbms_book Book ON R.book_id=Book.id "
					+"LEFT JOIN ( SELECT R.id AS id, U.`name` AS `name`, "
									+"C.renew_borrow_maxNum AS renewMaxNum "
									+"FROM bbms_user_reader R "
									+"LEFT JOIN bbms_user_info U "
									+"ON R.user_info_id=U.id "
									+"LEFT JOIN bbms_user_reader_category C ON C.id=R.category_id "
									+"WHERE 1=1 ) AS Reader ON R.user_reader_id=Reader.id "
					+"LEFT JOIN (SELECT M.id AS id, U.`name` AS `name` "
									+"FROM bbms_user_manager M "
									+"LEFT JOIN bbms_user_info U "
									+"ON M.user_info_id=U.id "
									+"WHERE 1=1 ) AS Manager ON R.user_manager_id=Manager.id "
					+"WHERE 1=1 ");
		
		count.append(    "SELECT COUNT(*) AS count "
						+"FROM bbms_borrow_record R " 
						+"LEFT JOIN bbms_book Book ON R.book_id=Book.id "
						+"LEFT JOIN ( SELECT R.id AS id, U.`name` AS `name` "
												+"FROM bbms_user_reader R "
												+"LEFT JOIN bbms_user_info U "
												+"ON R.user_info_id=U.id "
												+"WHERE 1=1 ) AS Reader ON R.user_reader_id=Reader.id "
						+"LEFT JOIN ( SELECT M.id AS id, U.`name` AS `name` "
												+"FROM bbms_user_manager M "
												+"LEFT JOIN bbms_user_info U "
												+"ON M.user_info_id=U.id "
												+"WHERE 1=1 ) AS Manager ON R.user_manager_id=Manager.id "
						+"WHERE 1=1 ");
		
		if (!StringUtil.isEmpty(s_book)) {
			sql.append(" AND Book.`name` LIKE '%"+ s_book +"%'");
			count.append(" AND Book.`name` LIKE '%"+ s_book +"%'");
		}
		if (!StringUtil.isEmpty(s_reader)) {
			sql.append(" AND Reader.`name` LIKE '%"+ s_reader +"%'");	
			count.append(" AND Reader.`name` LIKE '%"+ s_reader +"%'");
		}
		if (!StringUtil.isEmpty(s_manager)) {
			sql.append(" AND Manager.`name` LIKE '%"+ s_manager +"%'");
			count.append(" AND Manager.`name` LIKE '%"+ s_manager +"%'");
		}
		if (s_renewBorrowNum!=null) {
			sql.append(" AND R.renew_borrow_num="+ s_renewBorrowNum +"");
			count.append(" AND R.renew_borrow_num="+ s_renewBorrowNum +"");
		}
		if (s_status!=null) {
			sql.append(" AND R.`status`="+ s_status +"");
			count.append(" AND R.`status`="+ s_status +"");
		}
		if (!StringUtil.isEmpty(borrowStartTime)) {
			sql.append(" AND UNIX_TIMESTAMP(R.borrow_date) >=UNIX_TIMESTAMP('" + borrowStartTime + "')");
			count.append(" AND UNIX_TIMESTAMP(R.borrow_date) >=UNIX_TIMESTAMP('" + borrowStartTime + "')");
		}
		if (!StringUtil.isEmpty(borrowEndTime)) {
			sql.append(" AND UNIX_TIMESTAMP(R.borrow_date) <=UNIX_TIMESTAMP('" + borrowEndTime + "')");
			count.append(" AND UNIX_TIMESTAMP(R.borrow_date) <=UNIX_TIMESTAMP('" + borrowEndTime + "')");		
		}
		if (!StringUtil.isEmpty(restoreStartTime)) {
			sql.append(" AND UNIX_TIMESTAMP(R.restore_date) >=UNIX_TIMESTAMP('" + restoreStartTime + "')");
			count.append(" AND UNIX_TIMESTAMP(R.restore_date) >=UNIX_TIMESTAMP('" + restoreStartTime + "')");
		}
		if (!StringUtil.isEmpty(restoreEndTime)) {
			sql.append(" AND UNIX_TIMESTAMP(R.restore_date) <=UNIX_TIMESTAMP('" + restoreEndTime + "')");
			count.append(" AND UNIX_TIMESTAMP(R.restore_date) <=UNIX_TIMESTAMP('" + restoreEndTime + "')");
		}
		
	
		sql.append(" LIMIT " + ((currentPage - 1)* pageSize) + "," + pageSize +" ");
        
        System.out.println("SQL语句"+"		"+sql.toString());
		
        List<?> list=HibernateUtil.executeSqlQuery(sql.toString());
        Page page=new Page();
        page.setPageNo(currentPage);
        page.setPageSize(pageSize);
        page.setRows(list);
        
        List<HashMap> list2=(List<HashMap>)HibernateUtil.executeSqlQuery(count.toString());
        page.setTotal(Integer.parseInt(list2.get(0).get("count").toString()));
        
		return page;
	}

	/**
	 * 更新读者的借阅册数和借阅次数
	 */
	@Override
	public void updateReader(int readerId,int judg) {
		
		StringBuffer sql=new StringBuffer();
		if (judg==1) { 		//1表示借阅
			
			sql.append( "UPDATE bbms_user_reader AS reder "
					  + "SET reder.borrow_books_num=reder.borrow_books_num + 1, "
					  + "reder.borrow_num=reder.borrow_num + 1  "
					  + "WHERE reder.id="+ readerId +"");
			
		}else if(judg==0){	//0表示回滚/归还
			
			sql.append( "UPDATE bbms_user_reader AS reder "
					  + "SET reder.borrow_books_num=reder.borrow_books_num - 1, "
					  + "reder.borrow_num=reder.borrow_num - 1  "
					  + "WHERE reder.id="+ readerId +"");
		}
		
		System.out.println("SQL:"+sql.toString());
		
		HibernateUtil.executeSqlUpdate(sql.toString());
		
	}
	
	/**
	 * 更新图书借阅状态
	 * @param bookId 图书ID
	 * @param judg 判断更新类型
	 */
	@Override
	public void updateBook(int bookId, int judg) {
		
		StringBuffer sql=new StringBuffer();
		
		if (judg==0) { 		//0表示外借
			
			sql.append("UPDATE bbms_book book SET book.`status`=0 WHERE book.id="+ bookId +" ");
			
		}else if(judg==1){	//1表示在库
			
			sql.append("UPDATE bbms_book book SET book.`status`=1 WHERE book.id="+ bookId +" ");
		}
		
		System.out.println("SQL:"+sql.toString());
		
		HibernateUtil.executeSqlUpdate(sql.toString());
		
	}
	
	
	/**
	 * 根绝读者ID查找读者相关信息
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public List<HashMap> findReaderInfoById(int readerId) {
		
		StringBuffer sql=new StringBuffer();
		
		sql.append( "SELECT R.id AS readerId, R.borrow_books_num AS bookNum, C.borrow_books_maxNum AS maxBookNum, "
				  + "C.borrow_books_maxTime AS maxTime, C.renew_borrow_maxNum AS renewMaxNum, "
				  + "C.renew_borrow_maxTime AS renewMaxTime "
				  + "FROM bbms_user_reader R "
				  + "LEFT JOIN bbms_user_reader_category C "
				  + "ON R.category_id=C.id "
				  + "WHERE 1=1 "
				  + "AND R.id="+ readerId +" ");
		
		List<HashMap> list=(List<HashMap>)HibernateUtil.executeSqlQuery(sql.toString());
		
		return list;
	}

	/**
	 * 插入借阅记录
	 */
	@Override
	public void addBorrowRecord(BorrowRecord record, int borrowTime) {
		
		StringBuffer sql=new StringBuffer();
		
		sql.append( "INSERT INTO bbms_borrow_record(book_id, user_reader_id, user_manager_id, "
				  + "borrow_date, restore_date, renew_borrow_num, status ) "
				  + "VALUES("+ record.getBookId() +", "+ record.getReaderId() +", "
				  + ""+ record.getManagerId() +", NOW(),DATE_ADD(NOW(),INTERVAL "+ borrowTime +" DAY), 0, 0) ");
		
		HibernateUtil.executeSqlUpdate(sql.toString());
		
	}
	
	/**
	 * 更改记录
	 * @param record 记录实体对象
	 * @param borrowTime 可借阅天数
	 */
	@Override
	public void updateBorrowRecord(BorrowRecord record, int borrowTime, int judg) {
	
		StringBuffer sql=new StringBuffer();
		
		if (judg==1) {			//1表示修改
			
			sql.append(  "UPDATE bbms_borrow_record record "
						+"SET record.book_id="+ record.getBookId() +", record.user_reader_id="+ record.getReaderId() +","
						+"record.user_manager_id="+ record.getManagerId() + ",record.borrow_date=NOW(), "
						+"record.restore_date=DATE_ADD(NOW(),INTERVAL "+ borrowTime +" DAY), "
						+"record.renew_borrow_num=0, record.`status`=0  "
						+"WHERE record.id="+ record.getId() +" ");
			
		}else if(judg==0){		//0表示续借
			
			sql.append(  "UPDATE bbms_borrow_record record "
						+"SET record.book_id="+ record.getBookId() +", record.user_reader_id="+ record.getReaderId() +","
						+"record.user_manager_id="+ record.getManagerId() + ",record.borrow_date=NOW(), "
						+"record.restore_date=DATE_ADD(NOW(),INTERVAL "+ borrowTime +" DAY), "
						+"record.renew_borrow_num=record.renew_borrow_num + 1, record.`status`=0  "
						+"WHERE record.id="+ record.getId() +" ");
			
		}
		
		HibernateUtil.executeSqlUpdate(sql.toString());
		
	}


	/**
	 * 查找记录信息(修改记录的时候用到)
	 * @param recordId 记录ID
	 * @return 一条记录信息
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public List<HashMap> findRecordById(int recordId) {
		
		StringBuffer sql=new StringBuffer();
		
		sql.append( "SELECT record.id AS id, record.user_reader_id AS readerId, "
				  + "record.book_id AS bookId, record.user_manager_id AS managerId "
				  + "FROM bbms_borrow_record record "
				  + "WHERE record.id="+ recordId +" ");
							
		List<HashMap> list=(List<HashMap>)HibernateUtil.executeSqlQuery(sql.toString());
		
		return list;
	}

	/**
	 * 查找当前管理员
	 */
	@Override
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<HashMap> getCurrentManager(String account) {
		
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT manager.id AS id, userInfo.account AS account, userInfo.`name` AS `name` "
					+"FROM bbms_user_manager manager "
					+"LEFT JOIN bbms_user_info userInfo "
					+"ON manager.user_info_id=userInfo.id "
					+"WHERE 1=1 AND userInfo.account LIKE '%"+ account +"%'");
		
		List<HashMap> list=(List<HashMap>)HibernateUtil.executeSqlQuery(sql.toString());
		
		return list;
	}

}
